using System;
using System.Data;
using System.Data.SqlClient;
using Fadd.Data.Mappings;
using Xunit;

namespace Fadd.Data.ConnectionHelpers
{
    /// <summary>
    /// Connection helper for SQL Server.
    /// </summary>
    public class SqlServerHelper : IConnectionHelper
    {
        private readonly string _connectionString;

        /// <summary>
        /// Connection string
        /// </summary>
        /// <value></value>
        public string ConnectionString
        {
            get { return _connectionString; }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerHelper"/> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public SqlServerHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Create a new connection to the database.
        /// </summary>
        /// <returns>An open database connection.</returns>
        /// <exception cref="DataLayerException">If connection fails.</exception>
        public IDbConnection CreateConnection()
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                return connection;
            }
            catch(SqlException err)
            {
                throw new DataLayerException(null, "Failed to connect to '" + ConnectionString + "'.", err);
            }
        }

        /// <summary>
        /// Gets the paramter prefix
        /// </summary>
        /// <value>Will return a @</value>
        public string ParameterPrefix
        {
            get { return "@"; }
        }

		/// <summary>
		/// Try to get
		/// </summary>
		/// <param name="command">A command to perform actions with, we pass commands since they might be in a transaction.</param>
		/// <param name="sequenceName">Name of the sequence/generator.</param>
		/// <returns>A generated value.</returns>
		/// <exception cref="DataLayerException">If something failed </exception>
		/// <exception cref="NotSupportedException">SQL Server do not support sequences.</exception>
		public object GetNextSequenceValue(IDbCommand command, string sequenceName)
        {
            throw new NotSupportedException("SQL Server do not support sequences.");
        }

		/// <summary>
		/// Gets the latest auto incremented value from the database.
		/// </summary>
		/// <param name="command">A command to perform actions with, we pass commands since they might be in a transaction..</param>
		/// <param name="columnName">Name of the column that the auto incremented value is for.</param>
		/// <returns>auto incremented value</returns>
		/// <exception cref="DataLayerException">If something failed </exception>
        public object GetAutoIncrementValue(IDbCommand command, string columnName)
        {
            try
            {
				command.CommandText = "SELECT @@identity";
				return command.ExecuteScalar();
            }
            catch(SqlException err)
            {
                throw new DataLayerException(null, "Failed to fetch primary key value.", err);
            }
        }

        /// <summary>
        /// Modifies the SQL statement to include paging.
        /// </summary>
        /// <param name="mapping">Used to generate SQL Servers wierd paging method.</param>
        /// <param name="sqlStatement">SQL statement generated by the DAL.</param>
        /// <param name="page">0 based index</param>
        /// <param name="pageSize">number of entries per page</param>
        /// <returns>SQL statement with paging</returns>
        /// <example>
        /// 	<code>
        /// helper.Paging("SELECT * FROM users", 5, 10); // --&gt; "SELECT * FROM users LIMIT 10 OFFSET 50"
        /// </code>
        /// </example>
        /// <remarks>
        /// Only SQL Server 2005 and above is supported.
        /// </remarks>
        public string Paging(IMapping mapping, string sqlStatement, int page, int pageSize)
        {
        	//string orgSql = sqlStatement;
			/* WITH Ordered AS 
			 * (
				SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
				FROM Orders
			 * )
				SELECT *
				FROM Ordered
				WHERE RowNumber between 21 and 30*/

			// We need order by columns for the paging.
        	int orderByPos = sqlStatement.IndexOf("ORDER BY");
			if (orderByPos == -1)
			{
				orderByPos = sqlStatement.Length+1;
				sqlStatement += " ORDER BY ";
				foreach (var key in mapping.PrimaryKeys)
					sqlStatement += key.ColumnName + ", ";
				sqlStatement = sqlStatement.Remove(sqlStatement.Length - 2);
			}			
        	string order = sqlStatement.Substring(orderByPos);


			sqlStatement = sqlStatement.Insert(7, "ROW_NUMBER() OVER (" + order + ") AS FaddRowNumber, ");
        	sqlStatement = sqlStatement.Insert(0, "WITH Ordered AS (");
			orderByPos = sqlStatement.LastIndexOf("ORDER BY"); // need to reget it after the insert.

			// page = 2, pagesize = 5
			// ==============================
			// Page 1 = Row 1: (5 * (1-1)) + 1
			// Page 2 = Row 6: (5 * (2-1)) + 1
        	int rowStart = (pageSize*(page - 1)) + 1;
        	//int rowEnd = rowStart + pageSize;
            int rowEnd = rowStart + pageSize - 1; // Depends on the interpretation of BETWEEN by the server

        	string limit = "(FaddRowNumber BETWEEN " + rowStart + " AND " + rowEnd + ")";

            sqlStatement = sqlStatement.Insert(orderByPos-1, ") SELECT * FROM Ordered WHERE " + limit);
			
        	return sqlStatement;
        }

		[Fact]
		private static void TestWithOrderButNoWHERE()
		{
			SqlServerHelper helper = new SqlServerHelper("abcdef");
			Mapping mapping = new Mapping(helper.GetType(), "BullShit");
			mapping.Add("HelloWorld", "hello_world", true, "none");
			string sql = helper.Paging(mapping, "SELECT UserId, FirstName FROM Abc ORDER BY UserId", 1, 20);
            Assert.Equal("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY UserId) AS FaddRowNumber, UserId, FirstName FROM Abc) SELECT * FROM Ordered WHERE (FaddRowNumber BETWEEN 1 AND 20) ORDER BY UserId", sql);
		}

		[Fact]
		private static void TestWithoutOrderAndWhere()
		{
			SqlServerHelper helper = new SqlServerHelper("abcdef");
			Mapping mapping = new Mapping(helper.GetType(), "BullShit");
			mapping.Add("HelloWorld", "hello_world", true, "none");
			string sql = helper.Paging(mapping, "SELECT UserId, FirstName FROM Abc", 1, 20);
            Assert.Equal("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY hello_world) AS FaddRowNumber, UserId, FirstName FROM Abc) SELECT * FROM Ordered WHERE (FaddRowNumber BETWEEN 1 AND 20) ORDER BY hello_world", sql);
		}

		[Fact]
		private static void TestWithOrderAndWhere()
		{
			SqlServerHelper helper = new SqlServerHelper("abcdef");
			Mapping mapping = new Mapping(helper.GetType(), "BullShit");
			mapping.Add("HelloWorld", "hello_world", true, "none");
			string sql = helper.Paging(mapping, "SELECT UserId, FirstName FROM Abc WHERE UserId=? AND B=2 ORDER BY UserId, CreatedAt", 1, 20);
            Assert.Equal("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY UserId, CreatedAt) AS FaddRowNumber, UserId, FirstName FROM Abc WHERE UserId=? AND B=2) SELECT * FROM Ordered WHERE (FaddRowNumber BETWEEN 1 AND 20) ORDER BY UserId, CreatedAt", sql);
		}

		[Fact]
		private static void TestWithoutOrderAndWithWhere()
		{
			SqlServerHelper helper = new SqlServerHelper("abcdef");
			Mapping mapping = new Mapping(helper.GetType(), "BullShit");
			mapping.Add("HelloWorld", "hello_world", true, "none");
			string sql = helper.Paging(mapping, "SELECT UserId, FirstName FROM Abc WHERE UserId=? AND B=2", 1, 20);
            Assert.Equal("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY hello_world) AS FaddRowNumber, UserId, FirstName FROM Abc WHERE UserId=? AND B=2) SELECT * FROM Ordered WHERE (FaddRowNumber BETWEEN 1 AND 20) ORDER BY hello_world", sql);
		}
    }
}
